Databases

Two Main Approaches in Rust

Approach	Example Library	Description
ORM (Object Relational Mapper)	Diesel	Maps database tables → Rust structs
Query-based (SQL-first)	SQLx	You write raw SQL but get compile-time safety

Diesel — Strongly Typed ORM

Diesel is a type-safe ORM for Rust that:

• Uses Rust structs to represent database tables.
• Prevents SQL errors at compile time.
• Uses migrations and schema generation.
• Works with PostgreSQL, MySQL, and SQLite.

Step 1: Dependencies (Cargo.toml)

[dependencies]
diesel = { version = "2.1.0", features = ["postgres", "r2d2"] }
dotenvy = "0.15"
serde = { version = "1", features = ["derive"] }

Step 2: Database Setup (PostgreSQL)

Create a .env file:

DATABASE_URL=postgres://user:password@localhost/mydb

Initialize Diesel:

diesel setup
diesel migration generate create_users

Edit the migration files:

Up migration (up.sql)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

Down migration (down.sql)

DROP TABLE users;

Run:

diesel migration run

Step 3: Diesel Schema (schema.rs)

Diesel auto-generates:

diesel::table! {
    users (id) {
        id -> Int4,
        name -> Text,
        email -> Text,
    }
}

Step 4: Rust Models (models.rs)

use diesel::prelude::*;
use serde::{Deserialize, Serialize};

#[derive(Queryable, Serialize)]
pub struct User {
    pub id: i32,
    pub name: String,
    pub email: String,
}

#[derive(Insertable, Deserialize)]
#[diesel(table_name = crate::schema::users)]
pub struct NewUser {
    pub name: String,
    pub email: String,
}

Step 5: Database Operations (main.rs)

use diesel::prelude::*;
use diesel::pg::PgConnection;
use dotenvy::dotenv;
use std::env;

mod schema;
mod models;

use models::{User, NewUser};
use schema::users::dsl::*;

fn establish_connection() -> PgConnection {
    dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    PgConnection::establish(&database_url).expect("Error connecting to database")
}

// CREATE
fn create_user(conn: &mut PgConnection, new_user: NewUser) -> User {
    diesel::insert_into(users)
        .values(&new_user)
        .get_result(conn)
        .expect("Error saving new user")
}

// READ
fn get_users(conn: &mut PgConnection) -> Vec<User> {
    users.load::<User>(conn).expect("Error loading users")
}

fn main() {
    let conn = &mut establish_connection();

    let user = create_user(conn, NewUser {
        name: "Alice".to_string(),
        email: "alice@example.com".to_string(),
    });

    println!("Created user: {:?}", user);

    let all_users = get_users(conn);
    println!("All users: {:?}", all_users);
}

• Queryable: Maps DB rows → Rust structs.
• Insertable: Maps Rust struct → DB row.
• schema.rs: Ensures compile-time SQL safety.
• `Diesel prevents invalid queries at compile time.

SQLx — Async, SQL-First Library

SQLx is an async, non-blocking, SQL-first database library that:

• Uses raw SQL queries.
• Validates SQL at compile time.
• Supports PostgreSQL, MySQL, SQLite.
• Works perfectly with async web frameworks (Axum, Actix, Rocket).

Step 1: Dependencies (Cargo.toml)

[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "postgres", "macros"] }
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
dotenvy = "0.15"

Step 2: Database Setup

Same database table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

Step 3: Rust Code (main.rs)

use serde::{Deserialize, Serialize};
use sqlx::{PgPool, postgres::PgPoolOptions};
use dotenvy::dotenv;
use std::env;

#[derive(Debug, Serialize, Deserialize, sqlx::FromRow)]
struct User {
    id: i32,
    name: String,
    email: String,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&database_url)
        .await?;

    // CREATE
    let new_user = User {
        id: 0,
        name: "Bob".to_string(),
        email: "bob@example.com".to_string(),
    };

    let created: User = sqlx::query_as!(
        User,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, name, email",
        new_user.name,
        new_user.email
    )
    .fetch_one(&pool)
    .await?;

    println!("Created user: {:?}", created);

    // READ
    let users: Vec<User> = sqlx::query_as!(
        User,
        "SELECT id, name, email FROM users"
    )
    .fetch_all(&pool)
    .await?;

    println!("All users: {:?}", users);

    Ok(())
}

• query_as!: Checks SQL at compile time.
• $1, $2: Parameter placeholders.
• fetch_one, fetch_all: Execute queries asynchronously.
• PgPool: Connection pool for async operations.

Diesel vs SQLx — Comparison

Feature	Diesel	SQLx
Style	ORM	SQL-first
Async	❌ Mostly sync	✅ Fully async
Type Safety	Very high	Very high
Learning Curve	Steeper	Easier for SQL users
Best For	Complex data models, compile-time safety	Async web APIs, microservices

Which One Should You Use?

• Choose Diesel if:
  • You want ORM-style code.
  • You prefer compile-time query generation.
  • You’re building complex relational models.
• Choose SQLx if:
  • You want async database access.
  • You prefer writing SQL directly.
  • You’re building modern REST APIs with Axum/Actix/Rocket.